﻿using System;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Data.Models;
using System.Collections.Generic;
using AccessControl.Contracts.Models;
using System.Net;

namespace AccessControl.DataManagers.Tables.Managers
{
	/// <summary>
	/// Manages the enrties in the DB
	/// </summary>
	class EntriesManager : TableManager<IEntry>
	{
		#region Fields

		private List<IEntry> _entries;
		private SqlDataAdapter _adapter;
		private static EntriesManager s_Instance = null;

		private static readonly string COL_1_ID = "ID";
		private static readonly string COL_2_NAME = "NAME";
		private static readonly string COL_3_IP = "IP";
		private static readonly string COL_4_PORT_NUMBER = "PORT_NUMBER";

		#endregion

		#region Private Constructor

		/// <summary>
		/// Prevents a default instance of the <see cref="EntriesManager"/> class from being created.
		/// </summary>
		private EntriesManager()
		{			
		}

		#endregion

		#region Public Properties

		/// <summary>
		/// Gets the instance.
		/// </summary>
		public static EntriesManager Instance
		{
			get
			{
				lock (typeof(EntriesManager))
				{
					if (s_Instance == null)
						s_Instance = new EntriesManager();

					return s_Instance;
				}
			}
		}

		#endregion

		#region TableManager Overrides

		/// <summary>
		/// Creates using the specified connection.
		/// </summary>
		/// <param name="connection">The connection.</param>
		public override void Create(SqlConnection connection)
		{
			SqlCommand command = connection.CreateCommand();
			command.CommandText = string.Format("CREATE TABLE {0} ({1} INT, {2} TEXT, {3} TEXT, {4} INT)", TablesNames.ENTRIES, 
												 COL_1_ID, COL_2_NAME, COL_3_IP, COL_4_PORT_NUMBER);
			command.ExecuteNonQuery();
		}

		/// <summary>
		/// Initializes this instance.
		/// </summary>
		public override void Initialize(SqlConnection connection)
		{
			/* Create the adapter */
			_adapter = new SqlDataAdapter();
			/* Create the adapter commands */
			_adapter.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TablesNames.ENTRIES), connection);
			
			_adapter.InsertCommand = new SqlCommand((string.Format("INSERT INTO {0} ({1}, {2}, {3}, {4}) VALUES (@{1}, @{2}, @{3}, @{4})",
													 TablesNames.ENTRIES, COL_1_ID, COL_2_NAME, COL_3_IP, COL_4_PORT_NUMBER)), connection);
			
			_adapter.UpdateCommand = new SqlCommand((string.Format("UPDATE {0} SET {2} = @{2}, {3} = @{3}, {4} = @{4} WHERE {1} = @{1}",
													 TablesNames.ENTRIES, COL_1_ID, COL_2_NAME, COL_3_IP, COL_4_PORT_NUMBER)), connection);

			_adapter.DeleteCommand = new SqlCommand(string.Format("DELETE FROM {0} WHERE {1} = @{1}", 
													TablesNames.ENTRIES, COL_1_ID), connection);
		}

		/// <summary>
		/// Gets the list.
		/// </summary>
		/// <returns></returns>
		public override bool FillList(IList<IEntry> items)
		{
			Entry entry;
			List<ICamera> cameras = new List<ICamera>();
			List<ISensor> sensors = new List<ISensor>();

			/* Fill the cameras and sensors list */
			CamerasManager.Instance.FillList(cameras);
			SensorsManager.Instance.FillList(sensors);

			/* Get all the items */
			using (SqlDataReader reader = _adapter.SelectCommand.ExecuteReader())
			{
				while (reader.Read())
				{
					entry = new Entry()
					{
						Id = reader.GetInt32(0),
						Name = reader.GetString(1),
						Ip = IPAddress.Parse(reader.GetString(2)),
						PortNumber = reader.GetInt32(3)
					};

					/* Now find the matching sensor */
					foreach (ISensor sensor in sensors)
					{
						if (sensor.RelatedEntryId == entry.Id)
							entry.Sensors.Add(sensor);
					}

					/* And the camera */
					foreach (ICamera camera in cameras)
					{
						if (camera.RelatedEntryId == entry.Id)
							entry.Cameras.Add(camera);
					}

					/* Add the entry */
					items.Add(entry);
				}
			}

			return true;
		}

		/// <summary>
		/// Adds the specified item.
		/// </summary>
		/// <param name="item">The item.</param>
		public override bool Add(IEntry item)
		{
			_adapter.InsertCommand.Parameters.Clear();

			/* Set the item id */
			item.Id = GetHighestId(_adapter.SelectCommand.Connection, TablesNames.ENTRIES, COL_1_ID) + 1;

			_adapter.InsertCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", COL_1_ID), item.Id));
			_adapter.InsertCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", COL_2_NAME), item.Name));
			_adapter.InsertCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", COL_3_IP), item.Ip.ToString()));
			_adapter.InsertCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", COL_4_PORT_NUMBER), item.PortNumber));

			return (_adapter.InsertCommand.ExecuteNonQuery() > 0);
		}

		/// <summary>
		/// Updates the specified item.
		/// </summary>
		/// <param name="item">The item.</param>
		public override bool Update(IEntry item)
		{
			_adapter.UpdateCommand.Parameters.Clear();
			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", COL_1_ID), item.Id));
			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", COL_2_NAME), item.Name));
			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", COL_3_IP), item.Ip.ToString()));
			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", COL_4_PORT_NUMBER), item.PortNumber));
			
			int val = _adapter.UpdateCommand.ExecuteNonQuery();
			return true;
		}

		/// <summary>
		/// Deletes the specified item.
		/// </summary>
		/// <param name="item">The item.</param>
		public override bool Delete(IEntry item)
		{
			_adapter.DeleteCommand.Parameters.Clear();
			_adapter.DeleteCommand.Parameters.Add(new SqlParameter(string.Format("@{0}", COL_1_ID), item.Id));

			int val = _adapter.DeleteCommand.ExecuteNonQuery();
			return true;
		}

		#endregion
	}
}
